﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace WXY.Course.DAL
{
    public delegate T ReaderHandler<T>(DbDataReader reader);

    public class SqlHelper
    {
        private string _connectionString;

        public SqlHelper(string connectionString)
        {
            _connectionString = connectionString;
        }

        public int ExecuteNonQuery(string commandText, CommandType commandType, params DbParameter[] parameters)
        {
            
            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(con,cmd, commandText, commandType, parameters);

                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public object ExecuteScalar(string commandText, CommandType commandType, params DbParameter[] parameters)
        {
            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(con, cmd, commandText, commandType, parameters);

                    return cmd.ExecuteScalar();
                }
            }
        }

        public T ExecuteQuery<T>(string commandText, CommandType commandType, ReaderHandler<T> readerHandler, params DbParameter[] parameters)
        {
            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(con, cmd, commandText, commandType, parameters);

                    using (var reader = cmd.ExecuteReader())
                    {
                        return readerHandler(reader);
                    }
                }
            }
        }

        private void PrepareCommand(DbConnection conn, DbCommand cmd, string cmdText, CommandType commandType, params DbParameter[] parameters)
        {
            PrepareCommand(conn, cmd, cmdText, commandType, null, parameters);
        }

        private void PrepareCommand(DbConnection conn, DbCommand cmd, string cmdText, CommandType commandType, DbTransaction trans, params DbParameter[] parameters)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            if (trans != null)
                cmd.Transaction = trans;
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = commandType;
            if (parameters != null)
            {
                foreach (var parameter in parameters)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
    }
}